import express from 'express';
import sql from '../../sql.js';
const router = express.Router();
router.post('/searchArticle', async (req, res) => {
  const articleId = req.body.articleId;
    const categoryId = req.body.categoryId;
    const keyword = req.body.keyword;
    let page = parseInt(req.body.page) || 1;
    const pageSize = parseInt(req.body.pageSize) || 10;
    const labels = req.body.labels;
    const type = req.body.type;
    let articles

    // 构建查询基础部分
    let query = 'SELECT a.*, COUNT(l.ID) AS praise_count, ';
    query += '(SELECT JSON_ARRAYAGG(JSON_OBJECT(\'ID\', c.ID, \'user_id\', c.user_id, \'type\', c.type, \'create_time\', c.create_time, \'content\', c.content, \'report\', c.report, \'is_read\', c.is_read)) FROM comment c WHERE c.article_id = a.ID) AS comments ';
    query += 'FROM article a ';
    query += 'LEFT JOIN praise l ON a.ID = l.article_id ';
    let whereClauses = [];
    let values = [type];
    console.log(labels)

    // 根据参数添加条件

    if (articleId) {
        whereClauses.push('a.ID =?');
        values.push(articleId);
    }
    if (categoryId) {
        whereClauses.push('a.category_id =?');
        values.push(categoryId);
    }
    if (keyword) {
        whereClauses.push('(a.title LIKE? OR a.content LIKE? OR a.introduction LIKE?)');
        values.push(`%${keyword}%`, `%${keyword}%`, `%${keyword}%`);
    }
    if (labels && labels.length > 0) {
        // 使用 JSON_CONTAINS 函数来检查标签数组中是否包含指定的标签
        const labelConditions = labels.map(label => `JSON_CONTAINS(a.label_id, '"${label}"')`);
        whereClauses.push(`(${labelConditions.join(' OR ')})`);
    }

    query += ' WHERE a.type = ?'

    if (whereClauses.length > 0) {
        query += ' AND ' + whereClauses.join(' AND ');
    }

    // 分组和分页
    query += ' GROUP BY a.ID LIMIT ?,?';
    values.push((page - 1) * pageSize, pageSize);

    // 计算总数
    const countQuery = `SELECT COUNT(*) AS totalCount FROM article as a WHERE a.type= ? ${whereClauses.length > 0 ? 'AND ' + whereClauses.join(' AND ') : ''}`;
    const [countResult] = await sql.query(countQuery, values);
    const totalCount = countResult[0].totalCount;
    const totalPages = Math.ceil(totalCount / pageSize);
    
    try {
        [articles] = await sql.query(query, values);

        if (articles.length === 0 && page > 1) {
          page = page - 1;
          values[values.length-2] = (page - 1) * pageSize;
          const [data] = await sql.query(query, values);
          articles = data;
        }

        res.send({
            status: 'success',
            message: '文章查询成功',
            data: articles,
            pagination: {
                currentPage: page,
                pageSize: pageSize,
                totalCount: totalCount,
                totalPages: totalPages
            }
        });
    } catch (error) {
        console.error('查询错误：', error);
        res.send({
            status: 'error',
            message: '服务器内部错误',
            errorCode: 500
        });
    }
});

// 新建文章接口
router.post('/addArticle', async (req, res) => {
  let { title, category_id, type, label_id, introduction, content, image, state } = req.body;
  category_id = category_id  || 1;
  try { 
    // 检查标题是否已存在
    const checkQuery = 'SELECT * FROM article WHERE title =?';
    const [existingShares] = await sql.query(checkQuery, [title]);
    if (existingShares.length > 0) {
        return res.send({
            status: 'error',
            message: '标题已存在',
            errorCode: 409
        });
    }

    const query = `INSERT INTO article (title, category_id, type, label_id, introduction, content, image, state) VALUES (?,?,?,?,?,?,?,?)`;
    const [result] = await sql.query(query, [title, category_id, type, JSON.stringify(label_id), introduction, content, image, state]);
    const newArticleQuery = `SELECT * FROM article WHERE ID =?`;
    const [newArticle] = await sql.query(newArticleQuery, [result.insertId]);
    res.send({
        status: 'success',
        message: '文章创建成功',
        article: newArticle[0]
    });
  } catch (error) {
      console.error('创建文章错误：', error);
      res.send({
          status: 'error',
          message: '服务器内部错误',
          errorCode: 500
      });
  }
});

// 修改文章接口
router.put('/updateArticle/:articleId', async (req, res) => {
  const articleId = req.params.articleId;
  let { title, category_id, type, label_id, introduction, content, image, state } = req.body;
  try {
    // 检查标题是否已存在
    const checkQuery = 'SELECT * FROM article WHERE title =? AND ID!=?';
    const [existingShares] = await sql.query(checkQuery, [title, articleId]);
    if (existingShares.length > 0) {
        return res.send({
            status: 'error',
            message: '标题已存在',
            errorCode: 409
        });
    }

    let updateQuery = 'UPDATE article SET ';
    const values = [];
    if (title) {
        updateQuery += 'title =?, ';
        values.push(title);
    }
    if (category_id) {
        updateQuery += 'category_id =?, ';
        values.push(category_id);
    }
    if (type) {
        updateQuery += 'type =?, ';
        values.push(type);
    }
    if (label_id && label_id.length > 0) {
        updateQuery += 'label_id =?, ';
        values.push(JSON.stringify(label_id));
    }
    if (introduction) {
        updateQuery += 'introduction =?, ';
        values.push(introduction);
    }
    if (content) {
        updateQuery += 'content =?, ';
        values.push(content);
    }
    if (image) {
        updateQuery += 'image =?, ';
        values.push(image);
    }
    if (state !== undefined) {
        updateQuery += 'state =?, ';
        values.push(state);
    }
    // 去除末尾的逗号和空格
    updateQuery = updateQuery.slice(0, -2);
    updateQuery += ' WHERE ID =?';
    values.push(articleId);
    await sql.query(updateQuery, values);
    const updatedArticleQuery = 'SELECT * FROM article WHERE ID =?';
    const [updatedArticle] = await sql.query(updatedArticleQuery, [articleId]);
    res.send({
        status: 'success',
        message: '文章更新成功',
        article: updatedArticle[0]
    });
  } catch (error) {
      console.error('更新文章错误：', error);
      res.send({
          status: 'error',
          message: '服务器内部错误',
          errorCode: 500
      });
  }
});

// 删除文章接口
router.delete('/deleteArticle/:articleId', async (req, res) => {
  const articleId = req.params.articleId;
  try {
      const deleteQuery = 'DELETE FROM article WHERE ID =?';
      await sql.query(deleteQuery, [articleId]);
      res.send({
          status: 'success',
          message: '文章删除成功'
      });
  } catch (error) {
      console.error('删除文章错误：', error);
      res.send({
          status: 'error',
          message: '服务器内部错误',
          errorCode: 500
      });
  }
});

export default router;

